package SQLAPI;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author anvil
 */
public class SQLBroker 
{
    private static Connection connection = null;
    private final String username="postgres",ip="jdbc:postgresql://127.0.0.1:5432/anvildb",password="password";
    
    public boolean connect()
    {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your PostgreSQL JDBC Driver? "
                            + "Include in your library path!");
            e.printStackTrace();
            return false;
        }
        System.out.println("Driver is good!");
        
        
        try {
            
            connection = DriverManager.getConnection(
                            ip, username,
                            password);
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return false;
        }
        
        System.out.println("Connection is good!");
        return true;
    }
    
    public void toggleFlag(String username)
    {
        try {
            CallableStatement cs = connection.prepareCall("{?=changeFlag(?)}");
            cs.setString(1, username);
            cs.execute();
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public int getEmpId(String username)
    {
        int empid=0;
        final String sql = "SELECT EMPID FROM EMPLOYEE WHERE email="+username;
        try {
            Statement st = connection.createStatement();
            ResultSet rs=st.executeQuery(sql);
            while(rs.next())
            {
                empid=rs.getInt(1);
                if(empid==0)
                {
                    System.out.println("EmpID was not fetched properly");
                    return empid;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }         
        return empid;
    }
    
    public String getFirstName(String email)
    {
        String fname=null;
        final String sql = "SELECT FIRSTNAME FROM EMPLOYEE WHERE email="+username;
        try {
            Statement st = connection.createStatement();
            ResultSet rs=st.executeQuery(sql);
            while(rs.next())
            {
                fname=rs.getString(1);
                if(fname==null)
                {
                    System.out.println("First name was not fetched properly");
                    return fname;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }     
        return fname;
    }
    
    public ResultSet getAllUsers()
    {
        ResultSet rs=null;
        try {
            CallableStatement cs = connection.prepareCall("{?=call getAllWebUsers()}");
            rs = cs.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(rs!=null)
        {
            System.out.println("ResultSet is not null!");
            return rs;
        }
        return rs;
    }
    
    public ResultSet getUsageData()
    {
        ResultSet rs=null;
        
        try {
            final String sql ="SELECT * FROM USAGE_REPORT";
            Statement st = connection.createStatement();
            rs=st.executeQuery(sql);
            
        } catch (Exception ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(rs!=null)
        {
            System.out.println("ResultSet is not null!");
            return rs;
        }
        return rs;
    }
    
    public boolean insertUsageData(int empid, String message)
    {
        try {
            CallableStatement cs = connection.prepareCall("{?=call generateUsageReport(?,?)}");
            cs.setInt(1, empid);
            cs.setString(2,message);
            cs.execute();
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
    
    public ResultSet getTransactionData()
    {
        ResultSet rs = null;
        try {
            final String sql ="SELECT * FROM TRANSACTION_REPORT";
            Statement st = connection.createStatement();
            rs=st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
    
    public ResultSet getAllWebUsers()
    {
        ResultSet rs = null;
        try {
            final String sql ="SELECT * FROM WEB_USER";
            Statement st = connection.createStatement();
            rs=st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
    
    public void close()
    {
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(SQLBroker.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
